﻿using CommonBase;
using DBLib;
using EnvCommonUtility;
using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data;
using System.Data.OleDb;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace DAL
{
    /// <summary>
    /// 数据操作类，业务逻辑层
    /// </summary>
    public class IWMDal : DataOperation
    {
        #region 常量

        // TreeNode
        public const string QUERY_GET_TREENODEDATA = "select * from TreeNode where ParentID <> '0'"; // Tree 加载所有
        public const string QUERY_GET_TREENODEDATA_BYPARENTID = "select * from TreeNode where ParentID = '{0}'"; // 根据 ParentID 加载 Tree 列表，新增时
        public const string QUERY_GET_TREENODEDATA_BYID = "select * from TreeNode where ID = '{0}'"; // 修改时

        // Syllabus
        public const string QUERY_GET_SYLLABUSDATA_BYCLASSID = "select * from Syllabus where ClassID = '{0}'"; // 根据 ClassID 加载 Syllabus 列表

        #endregion

        #region 方法

        #region TreeNode 表操作

        /// <summary>
        /// 新增 TreeNode 项目
        /// </summary>
        /// <param name="connectionStringName">ConnectionString Name</param>
        /// <param name="mainProjectEXE">主工程可执行文件名称</param>
        /// <param name="id">主键</param>
        /// <param name="parentID">父结点</param>
        /// <param name="treeName">结点名称</param>
        /// <param name="treeAddress">位置</param>
        /// <param name="treeIP">IP/机器名</param>
        /// <param name="treePort">端口号</param>
        /// <param name="treeStatus">状态</param>
        /// <param name="treeTeacher">老师姓名</param>
        /// <param name="treeRemark">备注</param>
        /// <returns></returns>
        public int AddTreeNodeItem(string connectionStringName, string mainProjectEXE,
            string id, string parentID, string treeName,
            string treeAddress, string treeIP, string treePort,
            string treeStatus, string treeTeacher, string treeRemark)
        {
            if (string.IsNullOrWhiteSpace(id))
            {
                MessageBox.Show("ID 不为空！", ConstantString.MSG_COMPANY);
                return 0;
            }

            int resultCount = 0;
            DataSet dataSet = new DataSet();

            try
            {
                OleDbDataAdapter oleDbAdapter = GetDataAdapter(string.Format(QUERY_GET_TREENODEDATA_BYPARENTID, "0"), // 只获取数据格式
                    connectionStringName, mainProjectEXE);
                new OleDbCommandBuilder(oleDbAdapter);
                oleDbAdapter.Fill(dataSet, "TreeNode");

                DataRow newRow = dataSet.Tables["TreeNode"].NewRow(); // 添加一个新数据行
                // 添加数据
                newRow["ID"] = id;
                newRow["ParentID"] = parentID;
                newRow["TreeName"] = treeName;
                newRow["TreeAddress"] = treeAddress;
                newRow["TreeIP"] = treeIP;
                newRow["TreePort"] = treePort;
                newRow["TreeStatus"] = treeStatus;
                newRow["TreeTeacher"] = treeTeacher;
                newRow["TreeRemark"] = treeRemark;

                dataSet.Tables["TreeNode"].Rows.Add(newRow);
                resultCount = oleDbAdapter.Update(dataSet, "TreeNode");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, ConstantString.MSG_COMPANY);
            }
            finally
            {
                dataSet.Dispose();
            }

            return resultCount;
        }

        /// <summary>
        /// 修改 TreeNode 项目
        /// </summary>
        /// <param name="connectionStringName">ConnectionString Name</param>
        /// <param name="mainProjectEXE">主工程可执行文件名称</param>
        /// <param name="id">主键</param>
        /// <param name="parentID">父结点</param>
        /// <param name="treeName">结点名称</param>
        /// <param name="treeAddress">位置</param>
        /// <param name="treeIP">IP/机器名</param>
        /// <param name="treePort">端口号</param>
        /// <param name="treeStatus">状态</param>
        /// <param name="treeTeacher">老师姓名</param>
        /// <param name="treeRemark">备注</param>
        /// <returns></returns>
        public int EditTreeNodeItem(string connectionStringName, string mainProjectEXE,
            string id, string parentID, string treeName,
            string treeAddress, string treeIP, string treePort,
            string treeStatus, string treeTeacher, string treeRemark)
        {
            if (string.IsNullOrWhiteSpace(id))
            {
                MessageBox.Show("ID 不为空！", ConstantString.MSG_COMPANY);
                return 0;
            }

            int resultCount = 0;
            DataSet dataSet = new DataSet();

            try
            {
                OleDbDataAdapter oleDbAdapter = GetDataAdapter(string.Format(QUERY_GET_TREENODEDATA_BYID, id),
                    connectionStringName, mainProjectEXE);
                new OleDbCommandBuilder(oleDbAdapter);
                oleDbAdapter.Fill(dataSet, "TreeNode");

                if (dataSet.Tables["TreeNode"].Rows.Count <= 0)
                    return resultCount;

                // 修改数据
                dataSet.Tables["TreeNode"].Rows[0]["ParentID"] = parentID;
                dataSet.Tables["TreeNode"].Rows[0]["TreeName"] = treeName;
                dataSet.Tables["TreeNode"].Rows[0]["TreeAddress"] = treeAddress;
                dataSet.Tables["TreeNode"].Rows[0]["TreeIP"] = treeIP;
                dataSet.Tables["TreeNode"].Rows[0]["TreePort"] = treePort;
                dataSet.Tables["TreeNode"].Rows[0]["TreeStatus"] = treeStatus;
                dataSet.Tables["TreeNode"].Rows[0]["TreeTeacher"] = treeTeacher;
                dataSet.Tables["TreeNode"].Rows[0]["TreeRemark"] = treeRemark;

                resultCount = oleDbAdapter.Update(dataSet, "TreeNode");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, ConstantString.MSG_COMPANY);
            }
            finally
            {
                dataSet.Dispose();
            }

            return resultCount;
        }

        /// <summary>
        /// 批量根据 ID 删除 TreeNode 项目
        /// </summary>
        /// <param name="deleteList">删除对象列表</param>
        /// <param name="connectionStringName">ConnectionString Name</param>
        /// <param name="mainProjectEXE">主工程可执行文件名称</param>
        /// <returns></returns>
        public int DeleteTreeNodeItem(string connectionStringName, string mainProjectEXE,
            List<ITreeGroup> deleteList)
        {
            if (deleteList == null ||
                (deleteList != null && deleteList.Count <= 0))
            {
                MessageBox.Show("无数据删除！", ConstantString.MSG_COMPANY);
                return 0;
            }

            int resultCount = 0;
            DataSet dataSet = new DataSet();

            try
            {
                OleDbDataAdapter oleDbAdapter = GetDataAdapter(QUERY_GET_TREENODEDATA,
                    connectionStringName, mainProjectEXE);
                new OleDbCommandBuilder(oleDbAdapter);
                oleDbAdapter.Fill(dataSet, "TreeNode");

                for (int i = 0; i < deleteList.Count; i++)
                {
                    for (int j = 0; j < dataSet.Tables["TreeNode"].Rows.Count; j++)
                    {
                        if (dataSet.Tables["TreeNode"].Rows[j].RowState != DataRowState.Deleted &&
                            deleteList[i].Arg != null && deleteList[i].Arg is TagModel &&
                            dataSet.Tables["TreeNode"].Rows[j]["ID"] != null &&
                            dataSet.Tables["TreeNode"].Rows[j]["ID"].ToString().Equals((deleteList[i].Arg as TagModel).ID))
                        {
                            dataSet.Tables["TreeNode"].Rows[j].Delete();
                        }
                    }
                }

                resultCount = oleDbAdapter.Update(dataSet, "TreeNode");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, ConstantString.MSG_COMPANY);
            }
            finally
            {
                dataSet.Dispose();
            }

            return resultCount;
        }

        #endregion

        #region Syllabus 表操作

        /// <summary>
        /// 根据班级号获取课程表
        /// </summary>
        /// <param name="connectionStringName">ConnectionString Name</param>
        /// <param name="mainProjectEXE">主工程可执行文件名称</param>
        /// <param name="classID">班级号</param>
        /// <returns></returns>
        public ObservableCollection<SyllabusModel> GetSyllabusItemByClassID(string connectionStringName,
            string mainProjectEXE, string classID)
        {
            if (string.IsNullOrWhiteSpace(classID))
            {
                MessageBox.Show("班级序号不为空！", ConstantString.MSG_COMPANY);
                return null;
            }

            try
            {
                DataView dataSyllabus = GetDataSet(
                    string.Format(QUERY_GET_SYLLABUSDATA_BYCLASSID, classID), null,
                    connectionStringName, mainProjectEXE).Tables[0].DefaultView;
                ObservableCollection<SyllabusModel> syllabusList = new ObservableCollection<SyllabusModel>();

                foreach (DataRowView row in dataSyllabus)
                {
                    SyllabusModel syllabus = new SyllabusModel()
                    {
                        ClassID = row["ClassID"].ToString(),
                        ColumnTitleHeader = row["ColumnTitleHeader"].ToString(),
                        Monday = row["Monday"].ToString(),
                        Tuesday = row["Tuesday"].ToString(),
                        Wednesday = row["Wednesday"].ToString(),
                        Thursday = row["Thursday"].ToString(),
                        Friday = row["Friday"].ToString(),
                        Saturday = row["Saturday"].ToString(),
                        Sunday = row["Sunday"].ToString(),
                        Order = int.Parse(row["DisplayOrder"].ToString()),
                    };

                    syllabusList.Add(syllabus);
                }

                return syllabusList;
            }
            catch (System.Exception ex)
            {
                MessageBox.Show(ex.Message, ConstantString.MSG_COMPANY);
                return null;
            }
        }

        /// <summary>
        /// 批量添加
        /// </summary>
        /// <param name="connectionStringName">ConnectionString Name</param>
        /// <param name="mainProjectEXE">主工程可执行文件名称</param>
        /// <returns></returns>
        public int AddSyllabusItem(string connectionStringName, string mainProjectEXE,
            ObservableCollection<SyllabusModel> syllabusList)
        {
            if (syllabusList == null ||
                syllabusList.Count <= 0)
            {
                MessageBox.Show("无任何数据可添加！", ConstantString.MSG_COMPANY);
                return 0;
            }

            int resultCount = 0;
            DataSet dataSet = new DataSet();

            try
            {
                OleDbDataAdapter oleDbAdapter = GetDataAdapter(string.Format(QUERY_GET_SYLLABUSDATA_BYCLASSID, syllabusList.FirstOrDefault().ClassID), // 只获取数据格式
                    connectionStringName, mainProjectEXE);
                new OleDbCommandBuilder(oleDbAdapter);
                oleDbAdapter.Fill(dataSet, "Syllabus");

                DataRow newRow;
                foreach (SyllabusModel syllabus in syllabusList)
                {
                    newRow = dataSet.Tables["Syllabus"].NewRow(); // 添加一个新数据行
                    // 添加数据
                    newRow["ClassID"] = syllabus.ClassID;
                    newRow["ColumnTitleHeader"] = syllabus.ColumnTitleHeader;
                    newRow["Monday"] = syllabus.Monday;
                    newRow["Tuesday"] = syllabus.Tuesday;
                    newRow["Wednesday"] = syllabus.Wednesday;
                    newRow["Thursday"] = syllabus.Thursday;
                    newRow["Friday"] = syllabus.Friday;
                    newRow["Saturday"] = syllabus.Saturday;
                    newRow["Sunday"] = syllabus.Sunday;
                    newRow["DisplayOrder"] = syllabus.Order;

                    dataSet.Tables["Syllabus"].Rows.Add(newRow);
                }

                resultCount = oleDbAdapter.Update(dataSet, "Syllabus");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, ConstantString.MSG_COMPANY);
            }
            finally
            {
                dataSet.Dispose();
            }

            return resultCount;
        }

        /// <summary>
        /// 批量根据班级 ID 删除 Syllabus 项目
        /// </summary>
        /// <param name="connectionStringName">ConnectionString Name</param>
        /// <param name="mainProjectEXE">主工程可执行文件名称</param>
        /// <param name="classID">删除的班级序号</param>
        /// <returns>MaxValue 标志：无数据可以删除</returns>
        public int DeleteSyllabusItem(string connectionStringName, string mainProjectEXE,
            string classID)
        {
            if (string.IsNullOrWhiteSpace(classID))
            {
                MessageBox.Show("班级序号不为空！", ConstantString.MSG_COMPANY);
                return 0;
            }

            int resultCount = 0;
            DataSet dataSet = new DataSet();

            try
            {
                OleDbDataAdapter oleDbAdapter = GetDataAdapter(
                    string.Format(QUERY_GET_SYLLABUSDATA_BYCLASSID, classID),
                    connectionStringName, mainProjectEXE);
                new OleDbCommandBuilder(oleDbAdapter);
                oleDbAdapter.Fill(dataSet, "Syllabus");

                if (dataSet.Tables["Syllabus"].Rows.Count == 0)
                {
                    return int.MaxValue; // 标志：无数据可以删除！
                }

                foreach (DataRow row in dataSet.Tables["Syllabus"].Rows) // 将所有数据清空
                {
                    row.Delete();
                }

                resultCount = oleDbAdapter.Update(dataSet, "Syllabus");
            }
            catch (System.Exception ex)
            {
                MessageBox.Show(ex.Message, ConstantString.MSG_COMPANY);
            }
            finally
            {
                dataSet.Dispose();
            }

            return resultCount;
        }

        #endregion

        #endregion
    }
}
